﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace TuyenDungViecLam.User_control_VN
{
    public partial class TimKiemCVonline : System.Web.UI.UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        string cn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        public DataTable ExecuteQuery(string strSQL)
        {

            DataTable dt = new DataTable();
            try
            {
                SqlConnection connect = new SqlConnection(cn);
                connect.Open();
                SqlCommand command = connect.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = strSQL;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;

                adapter.Fill(dt);
                connect.Close();
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            return dt;

        }

        public int ExecuteNonQuery(string strSQL)
        {
            SqlConnection connect = new SqlConnection(cn);
            connect.Open();
            int kq;
            try
            {
                SqlCommand command = connect.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = strSQL;
                kq = command.ExecuteNonQuery();
                connect.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return kq;
        }
        protected void btnTimCV(object sender, EventArgs e)
        {
            string timkiem = "TimKiemCVOnline.aspx";
            string QueryString = "";
            string namkinhnghiem = "0";
            string trinhdo = "0";
            float mucluong1 = 0;
            float mucluong2 = 0;
            if (cbxAdvance.Checked)
            {
                ListItem litNKN = lbNamKinhNghiem.Items[lbNamKinhNghiem.SelectedIndex];
                namkinhnghiem = litNKN.Value;

                ListItem litTD = lbTrinhDo.Items[lbTrinhDo.SelectedIndex];
                trinhdo = litTD.Value;

                ListItem litML = ListBox1.Items[ListBox1.SelectedIndex];
                //ListItem litML = ddlMucLuong.Items[ddlMucLuong.SelectedIndex];
                mucluong1 = float.Parse(litML.Value);
                // truong hop luong lon hon 20
                //mucluong2;
                if (mucluong1 == 20)
                {
                    mucluong2 = mucluong1 * 10000;
                }
                else // cac truong hop con lai
                {
                    mucluong2 = mucluong1 * 2;
                }

                
                // truong hop 1
                if (namkinhnghiem != "0" && trinhdo != "0" && mucluong1 > (float)0)
                {
                    QueryString = "select * from CVONLINE where (NamKinhNghiem = " + "'" + namkinhnghiem + "'" + " and ";
                    QueryString += "TrinhDo = " + "'" + trinhdo + "'" + " and MucLuong >= " + mucluong1.ToString() + " and MucLuong <= " + mucluong2.ToString() + ") or ";
                    QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                    timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                }
                // truong hop 2
                if (namkinhnghiem != "0" && trinhdo != "0" && mucluong1 == (float)0)
                {
                    QueryString = "select * from CVONLINE where (NamKinhNghiem = " + "'" + namkinhnghiem + "'" + " and ";
                    QueryString += "TrinhDo = " + "'" + trinhdo + "'" + ") or ";
                    QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                    timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                }
                // truong hop 3
                if (namkinhnghiem != "0" && trinhdo == "0" && mucluong1 > (float)0)
                {
                    QueryString = "select * from CVONLINE where (NamKinhNghiem = " + "'" + namkinhnghiem + "'" + " and ";
                    QueryString += " MucLuong >= " + mucluong1.ToString() + " and MucLuong <= " + mucluong2.ToString() + ") or ";
                    QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                    timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                }
                // truong hop 4
                if (namkinhnghiem == "0" && trinhdo != "0" && mucluong1 > (float)0)
                {
                    QueryString = "select * from CVONLINE where ( ";
                    QueryString += "TrinhDo = " + "'" + trinhdo + "'" + " and MucLuong >= " + mucluong1.ToString() + " and MucLuong <= " + mucluong2.ToString() + ") or ";
                    QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                    timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                }
                // truong hop 5
                if (namkinhnghiem == "0" && trinhdo == "0" && mucluong1 == (float)0)
                {
                    QueryString = "select * from CVONLINE where (NamKinhNghiem like " + "'" + txtTuKhoa.Text + "'" + " or ";
                    QueryString += "TrinhDo like " + "'" + txtTuKhoa.Text + "'"+" ) or ";
                    QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                    timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                }
                    // truong hop 6
                    if (namkinhnghiem == "0" && trinhdo == "0" && mucluong1 > (float)0)
                    {
                        QueryString = "select * from CVONLINE where ";
                        QueryString += " MucLuong >= " + mucluong1.ToString() + " and MucLuong <= " + mucluong2.ToString() + ") or ";
                        QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                        timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                    }
                    // truong hop 7
                    if (namkinhnghiem == "0" && trinhdo != "0" && mucluong1 == (float)0)
                    {
                        QueryString = "select * from CVONLINE where ";
                        QueryString += "TrinhDo = " + "'" + trinhdo + "'" + " or";
                        QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                        timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                    }
                    // truong hop 8
                    if (namkinhnghiem != "0" && trinhdo == "0" && mucluong1 == (float)0)
                    {
                        QueryString = "select * from CVONLINE where NamKinhNghiem = " + "'" + namkinhnghiem + "'" + " or ";
                        QueryString += "( HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + "DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                        timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                    }


                    DataTable db = ExecuteQuery(QueryString);
                    
                    //GridView1.DataSource = db;
                    
                    //GridView1.DataBind();

                    int i = db.Rows.Count;
                    string query1;
                    int kq;
                    string userid = Session["UserID"].ToString();
                    string QueryString2 = "select * from NHATUYENDUNG where USER_ID = " + userid;
                    DataTable db2 = ExecuteQuery(QueryString2);
                    DataRow dr2 = db2.Rows[0];
                    int MaNhaTD = int.Parse(dr2["MaNhaTuyenDung"].ToString());
                    
                    for (; i > 0; )
                    {
                        DataRow dr = db.Rows[i - 1];
                        int macv = int.Parse(dr["MaCV"].ToString());
                        string ngay = DateTime.Today.ToString();
                        
                        // kiem tra nha tuyen dung da xem CVONLINE này chưa
                        string querykt = "select * from NHATUYENDUNG_XEM_CVONLINE where MaNhaTuyenDung = " + MaNhaTD.ToString() + " and MaCV = " + macv.ToString();
                        DataTable dt1 = ExecuteQuery(querykt);

                        // insert
                        if (dt1.Rows.Count == 0)
                        {
                            query1 = "INSERT NHATUYENDUNG_XEM_CVONLINE(MaNhaTuyenDung,MaCV,NgayXem) values(";
                            query1 += MaNhaTD.ToString() + "," + macv.ToString() + ",'" + ngay + "')";
                            kq = ExecuteNonQuery(query1);
                        }
                        i--;
                       
                    }
                }
                else
                {
                    QueryString = "select * from CVONLINE where (NamKinhNghiem like " + "'" + txtTuKhoa.Text + "'" + " or ";
                    QueryString += "TrinhDo like " + "'" + txtTuKhoa.Text + "'" +") or ";
                    QueryString += "(HoTen like " + "'" + txtTuKhoa.Text + "'" + " or " + " DiaChi like " + "'" + txtTuKhoa.Text + "'" + " or " + "ViTriMongMuon like " + "'" + txtTuKhoa.Text + "'" + " or " + "GioiThieu like " + "'" + txtTuKhoa.Text + "')";

                    timkiem = "TimKiemCVOnline.aspx?TuKhoa=" + txtTuKhoa.Text + "&NamKinhNghiem=" + namkinhnghiem + "&TrinhDo=" + trinhdo + "&MucLuong1=" + mucluong1.ToString() + "&MucLuong2=" + mucluong2.ToString();
                    // luu thong tin nha tuyen dung xem CV online
                    DataTable db = ExecuteQuery(QueryString);
                    int i = db.Rows.Count;
                    string query1;
                    int kq;
                    string userid = Session["UserID"].ToString();
                    string QueryString2 = "select * from NHATUYENDUNG where USER_ID = " + userid;
                    DataTable db2 = ExecuteQuery(QueryString2);
                    DataRow dr2 = db2.Rows[0];
                    int MaNhaTD = int.Parse(dr2["MaNhaTuyenDung"].ToString());
                    for (; i > 0; )
                    {
                        DataRow dr = db.Rows[i - 1];
                        int macv = int.Parse(dr["MaCV"].ToString());
                        string ngay = DateTime.Today.ToString();

                        // kiem tra nha tuyen dung da xem CVONLINE này chưa
                        string querykt = "select * from NHATUYENDUNG_XEM_CVONLINE where MaNhaTuyenDung = " + MaNhaTD.ToString() + " and MaCV = " + macv.ToString();
                        DataTable dt1 = ExecuteQuery(querykt);

                        // insert
                        if (dt1.Rows.Count == 0)
                        {
                            query1 = "INSERT NHATUYENDUNG_XEM_CVONLINE(MaNhaTuyenDung,MaCV,NgayXem) values(";
                            query1 += MaNhaTD.ToString() + "," + macv.ToString() + ",'" + ngay + "')";
                            kq = ExecuteNonQuery(query1);
                        }
                        i--;
                    }
                }
                Response.Redirect(timkiem);
            }
        }
}
